package com.fancy.application.tools.autoddl.service.Impl;

import cn.hutool.core.util.StrUtil;
import com.fancy.application.tools.autoddl.constants.DataBaseTypeEnum;
import com.fancy.application.tools.autoddl.model.ColumnsModel;
import com.fancy.application.tools.autoddl.model.TableModel;
import lombok.extern.log4j.Log4j2;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Log4j2
@Service
public class MysqlTableManageService extends DatabaseTableManageService {

    public MysqlTableManageService() {
        this.databaseType = DataBaseTypeEnum.MYSQL;
        javaTypeMap = new HashMap<>();
        javaTypeMap.put("Integer","int");
        javaTypeMap.put("int","int");
        javaTypeMap.put("Double", "double");
        javaTypeMap.put("Boolean", "bit");
        javaTypeMap.put("String", "varchar");
        javaTypeMap.put("JSON", "json");
        javaTypeMap.put("Date", "datetime");
        javaTypeMap.put("time", "time");
    }




    @Override
    protected void getTableInfo() {
        this.tableModels = getTableNames();
    }



    //获取数据库名称
    private String getDataBaseName(){
        String sql = "select database()";
        return this.getJdbcTemplate().queryForObject(sql,String.class);
    }

    /**
     * 获取表名列表
     * @return
     */
    private List<TableModel> getTableNames(){
        String dataBaseName = getDataBaseName();
        String sql = "select table_name from information_schema.tables where table_schema=?";
        List<TableModel> tableModels = new ArrayList<>();

        List<String> tableNames = this.getJdbcTemplate().queryForList(sql,String.class,dataBaseName);


        tableNames.forEach(s -> {
            List<ColumnsModel> columnsModels = getColumnsByTableName(s,dataBaseName);
            TableModel tableModel = new TableModel(s);
            tableModel.getColumnsModelList().addAll(columnsModels);
            tableModels.add(tableModel);
        });
        return tableModels;
    }

    /**
     * 根据表名和库名来获取字段信息
     * @param tableName 表名
     * @param dataBaseName  数据库名
     * @return 字段列表
     */
    private List<ColumnsModel> getColumnsByTableName(String tableName,String dataBaseName) {
        String sql = "select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,COLUMN_KEY,CHARACTER_MAXIMUM_LENGTH " +
                        "from information_schema.COLUMNS where table_name = ? and TABLE_SCHEMA=?";
        SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql,tableName,dataBaseName);
        List<ColumnsModel> columnsModelList = new ArrayList<>();
        while(rs.next()){
//            String TABLE_SCHEMA = rs.getString("TABLE_SCHEMA");
//            String TABLE_NAME = rs.getString("TABLE_NAME");
            String COLUMN_NAME = rs.getString("COLUMN_NAME");   //字段名
            String COLUMN_DEFAULT = rs.getString("COLUMN_DEFAULT"); //默认值
            String IS_NULLABLE = rs.getString("IS_NULLABLE");   //是否为空
            String DATA_TYPE = rs.getString("DATA_TYPE");       //字段类型
            String COLUMN_KEY = rs.getString("COLUMN_KEY");     //是否为主键
            String CHARACTER_MAXIMUM_LENGTH = rs.getString("CHARACTER_MAXIMUM_LENGTH"); //字段长度
            ColumnsModel columnsModel = new ColumnsModel();
            if(StrUtil.isBlank(COLUMN_NAME)){
                log.warn("无法找到该字段，请检查！！已自动跳过");
                continue;
            }
            columnsModel.setName(COLUMN_NAME);
            if(StrUtil.isNotBlank(COLUMN_KEY)&&StrUtil.equals("PRI",COLUMN_KEY)){
                columnsModel.setPrimaryKey(true);
            }
            if(StrUtil.isNotBlank(IS_NULLABLE)&&StrUtil.equals("NO",IS_NULLABLE)){
                columnsModel.setNull(true);
            }
            if(StrUtil.isNotBlank(COLUMN_DEFAULT)){
                columnsModel.setDefaultValue(COLUMN_DEFAULT);
            }
            if(StrUtil.isNotBlank(COLUMN_DEFAULT)){
                columnsModel.setDefaultValue(COLUMN_DEFAULT);
            }
            if(StrUtil.isNotBlank(DATA_TYPE)){
                columnsModel.setColumnType(DATA_TYPE);
            }
            if(StrUtil.isNotBlank(CHARACTER_MAXIMUM_LENGTH)){
                Integer len = Integer.valueOf(CHARACTER_MAXIMUM_LENGTH);
                columnsModel.setLength(len);
            }else{
                columnsModel.setLength(-1);
            }
            columnsModelList.add(columnsModel);
        }
        return columnsModelList;
    }

    @Override
    protected List<String> generateSql() {
        List<String> sqlList = new ArrayList<>();
        unCreateTableList.forEach(tableModel -> {
            String sql = getCreateSQL(tableModel);
            sqlList.add(sql);

            log.debug("create sql---{}",sql);
        });
        unAddedColumnList.forEach(tableModel -> {
            List<String> alterSQLs = getAlterSQLs(tableModel);
            sqlList.addAll(alterSQLs);

        });
        return sqlList;
    }

    private List<String> getAlterSQLs(TableModel tableModel) {
        List<String> alterSQLs = new ArrayList<>();
        tableModel.getColumnsModelList().forEach(columnsModel -> {
            String sql = "ALTER TABLE "+tableModel.getName()+" add "+generatSql(columnsModel);
            alterSQLs.add(sql);
            log.debug("alter sql---{}",sql);
        });
        return alterSQLs;
    }

    /**
     * 获取建表的sql
     * @return sql
     */
    private String getCreateSQL(TableModel tableModel){
        String primaryKeySql = getPrimaryKey(tableModel.getColumnsModelList());
        String columnSql = getColumnSql(tableModel.getColumnsModelList());
        if(StrUtil.isNotBlank(primaryKeySql)){
            columnSql =columnSql+ primaryKeySql+" USING BTREE\n";
        }else{
            columnSql =StrUtil.subBefore(columnSql,",",true);
        }

        return "CREATE TABLE `"+ tableModel.getName()+"` (\n" +
                columnSql+
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
    }




    private String getPrimaryKey(List<ColumnsModel> columnsModelList) {
        String sql = "";
        for(ColumnsModel column: columnsModelList){
            if(column.isPrimaryKey()){
                sql = "PRIMARY KEY (`"+column.getName()+"`)";
                break;
            }
        }
        return sql;
    }

    private String getColumnSql(List<ColumnsModel> columnsModelList) {
        StringBuilder sql = new StringBuilder();
        for(ColumnsModel column: columnsModelList){
            String columnSql = "";
            columnSql += generatSql(column);
            columnSql+= ",\n";
            sql.append(columnSql);
        }

        return sql.toString();
    }

    private String generatSql(ColumnsModel column) {
        String columnSql ="";
        String columnType = column.getColumnType();
        if(StrUtil.isBlank(columnType)) columnType = "";
        switch (columnType){
            case "int":
                columnSql += "`"+column.getName()+"` int  ";
                break;
            case "time":
                columnSql += "`"+column.getName()+"` time  ";
                break;
            case "datetime":
                columnSql += "`"+column.getName()+"` datetime  ";
                break;
            case "double":
                columnSql += "`"+column.getName()+"` double("+column.getLength()+",2) ";
                break;
            case "json":
                columnSql += "`"+column.getName()+"` json ";
                break;
            case "bit":
                columnSql += "`"+column.getName()+"` bit ";
                break;
            default:
                columnSql += "`"+column.getName()+"` varchar("+column.getLength()+") ";
                break;
        }
        columnSql+= column.getDefaultValue();
        String nullAbleStr = column.isNull()?" null":" not null";
        columnSql+= nullAbleStr;
        return columnSql;
    }

}
